-- Active: 1713856700339@@127.0.0.1@5432@adonis@public
create or replace
function stock_report(
  date VARCHAR,
  param_goods_id int,
  param_warehouse_id INT,
  returnType INT
)
returns table (
  	goods_id int,
		goods_name varchar(100),
		warehouse_id int,
		warehouse_name varchar(30),
		fk_category_id int,
		unit json,
		quantity float,
		safety_quan float,
		max_safe_quan float,
		amt float,
		amt_cost float,
		price float,
		cost float,
		provider varchar(20),
		stat_type varchar(20),
		goods_spec varchar(40),
		cost_price float,
		cost_price_amt float
) as $$
begin
    drop table if exists t_remain_stock;

drop table if exists t_out_stock;

create temp table t_remain_stock (
      	goods_id int null,
        goods_name varchar(100) null,
        warehouse_id int null,
        warehouse_name varchar(30) null,
        fk_category_id int null,
        unit json null,
        quantity float null,
        safety_quan float null,
        max_safe_quan float null,
        amt float null,
        amt_cost float null,
        price float null,
        cost float null,
        provider varchar(20) null,
        stat_type varchar(20) null,
        goods_spec varchar(40) null,
        cost_price float null,
        cost_price_amt float null
    );

create temp table t_out_stock (
        goods_id INT null,
        warehouse_id INT null,
        unit json null,
        quantity float null,
        amt float null
    );

insert
	into
	t_remain_stock (goods_id,
	warehouse_id,
	quantity,
	amt)
    select
	fk_goods_id,
	-- 商品ID
	fk_warehouse_id,
	-- 仓库ID
	SUM(sid.quantity),
	-- 总数量
	SUM(unit_price)
	-- 总金额
from
	stock_in_details sid
	-- where billDate < start_date
where
	(param_goods_id is null
		or fk_goods_id = param_goods_id)
	and (param_warehouse_id is null
		or fk_warehouse_id = param_warehouse_id)
group by
	fk_goods_id,
	fk_warehouse_id;

insert
	into
	t_out_stock (goods_id,
	warehouse_id,
	quantity,
	amt)
    select
	fk_goods_id,
	-- 商品ID
	fk_warehouse_id,
	-- 仓库ID
	sum(sod.quantity),
	-- 总数量
	sum(unit_price)
	-- 总金额
from
	stock_out_details sod
	-- where billDate < start_date
where
	(param_goods_id is null
		or fk_goods_id = param_goods_id)
	and (param_warehouse_id is null
		or fk_warehouse_id = param_warehouse_id)
group by
	fk_goods_id,
	fk_warehouse_id;

update
	t_remain_stock t
set
	quantity = t.quantity - a.quantity,
	amt = t.amt - a.amt
from
	t_out_stock a
where
	a.goods_id = t.goods_id
	and a.warehouse_id = t.warehouse_id;

delete
from
	t_out_stock a
		using t_remain_stock t
where
	a.goods_id = t.goods_id
	and a.warehouse_id = t.warehouse_id;

insert
	into
	t_remain_stock(goods_id,
	warehouse_id,
	quantity,
	amt)
    select
	tos.goods_id,
	tos.warehouse_id,
	0 - tos.quantity,
	tos.amt
from
	t_out_stock tos;

update
	t_remain_stock a
set
	goods_name = b.name,
	fk_category_id = b.fk_category_id,
	goods_spec = b.spec
from
	goods b
where
	a.goods_id = b.id;
-- 使用 INNER JOIN 来进行表连接，并且添加额外的条件
    update
	t_remain_stock a
set
	warehouse_name = b.name
from
	dicts b
where
	a.warehouse_id = b.id
	and b.class_name = 'warehouse';

case
	when returnType = 1 then
        return QUERY
select
	*
from
	t_remain_stock;
when returnType = 2 then
        return QUERY
select
	goods_id,
	goods_name,
	warehouse_id,
	warehouse_name,
	fk_category_id,
	unit,
	quantity,
	safety_quan,
	max_safe_quan,
	amt,
	amt_cost,
	price,
	cost,
	provider,
	stat_type,
	goods_spec,
	cost_price,
	cost_price_amt
from
	t_remain_stock
union all
        select
	id as goods_id,
	name as goods_name,
	fk_warehouse_id as warehouse_id,
	warehouse_name,
	fk_category_id,
	null as unit,
	null as quantity,
	null as safety_quan,
	null as max_safe_quan,
	null as amt,
	null as amt_cost,
	null as price,
	cost,
	null as provider,
	null as stat_type,
	spec as goods_spec,
	null as cost_price,
	null as cost_price_amt
from
	goods
where
	not exists (
	select
		1
	from
		t_remain_stock
	where
		t_remain_stock.goods_id = goods.id
        );
else
        return QUERY
select
	returnType;
end case;
end;

$$ language plpgsql;
